create database Alluring_Decors
go
use Alluring_Decors
go
create table tblFAQs(
	faqID int primary key identity(1,1),
	faqQuestion ntext not null,
	faqContent ntext not null
)
go
create table tblHomePage(
	homeID int primary key identity(1,1),
	homeTitle nvarchar(100) not null,
	homeIMG nvarchar(100) not null,
	homeContent ntext not null
)
go
create table tblAboutUs(
	aboutID int primary key identity(1,1),
	aboutIMG nvarchar(100) not null,
	aboutContent ntext not null
)
go
create table tblAccounts(
	accID int primary key identity(1,1),
	accUserName nvarchar(50) not null,
	accPass nvarchar(50) not null,
	accFullName nvarchar(50) not null,
	accMobile varchar(20) not null,
	accAddress nvarchar(100),
	accEmail nvarchar(100),
	roles bit not null,
	accStatus int
)
go
create table tblFeedBacks(
	fbID int primary key identity(1,1),
	fbTitle nvarchar(100) not null,
	fbName nvarchar(100) not null,
	fbDate datetime not null,
	fbContent nvarchar(500) not null
)
go
create table tblContactUS(
	contactID int primary key identity(1,1),
	contactContent nvarchar(500) not null
)
go
create table tblOrderStatus(
	orderStaID int primary key identity(1,1),
	orderStaName nvarchar(50) not null
)
go
create table tblOrders(
	orderID int primary key identity(1,1),
	orderDate datetime not null,
	orderShipAddress nvarchar(200) not null,
	orderReceivedName nvarchar(30) not null,
	orderReceivedPhone varchar(20) not null,
	cusID int FOREIGN KEY REFERENCES tblAccounts(accID),
	orderStaID int FOREIGN KEY REFERENCES tblOrderStatus(orderStaID),
	orderTotalPrice decimal(10,2) not null
)
go
create table tblCategories(
	catID int primary key identity(1,1),
	catName nvarchar(100) not null
)
go
create table tblProjects(
	proID int primary key identity(1,1),
	proName nvarchar(50) not null,
	catID int FOREIGN KEY REFERENCES tblCategories(catID),
	proShortDes nvarchar(500),
	proDes nvarchar(4000),
	imageURL nvarchar(50) not null
)
go
create table tblDomainList(
	domainID int primary key identity(1,1),
	domainTitle nvarchar(100) not null,
	domainIMG varchar(100) not null,
	domainContent nvarchar(1000) not null
)
go
create table tblServiceOffered(
	offerID int primary key identity(1,1),
	domainID int FOREIGN KEY REFERENCES tblDomainList(domainID),
	offerName nvarchar(200) not null,
	offerPrice decimal(10,2) not null,
	offerIMG varchar(100) not null,
	offerShortDes nvarchar(500),
	offerDes nvarchar(4000) not null
)
go
create table tblOrderDetails(
	ordID int primary key identity(1,1),
	orderID int FOREIGN KEY REFERENCES tblOrders(orderID),
	offerID int FOREIGN KEY REFERENCES tblServiceOffered(offerID),
	orderDePrice decimal(10,2) not null,
	orderQuantity int not null
)
go
create table tblComments(
	comID int primary key identity(1,1),
	proID int FOREIGN KEY REFERENCES tblProjects(proID),
	comUserName nvarchar(30) not null,
	comDate datetime not null,
	comContent nvarchar(1000) not null
)
go
create table tblProjectDetails(
	projectID int FOREIGN KEY REFERENCES tblProjects(projectID),
	proID int FOREIGN KEY REFERENCES tblServiceOffered(proID),
	primary key (projectID,proID),
	price money not null
)


------------------------- PROCEDURE ----------------------------

go
create procedure sp_Register
@username nvarchar(50),
@password nvarchar(50),
@fullname nvarchar(50),
@phone nvarchar(20),
@address nvarchar(100),
@email nvarchar(100),
@role bit
as
insert into tblAccounts(accUserName,accPass,accFullName,accMobile,accAddress,accEmail,roles)values(@username,@password,@fullname,@phone,@address,@email,@role)
go
--exec sp_Register 'thanhdat','123','Tong Thanh Dat','0979785729','Ninh Binh','tongthanhdat@gmail.com',1
go
create procedure sp_Feedback
@title nvarchar(100),
@name nvarchar(100),
@date datetime,
@content nvarchar(500)
as
insert into tblFeedBacks values(@title,@name,@date,@content)
go
--exec sp_Feedback 'abc','nguyen van a','2012/10/09','dsfjkldsjkfljdsfdjsflkjdsklfjds'
go



-------------- INSERT TABLE -----------------
GO
Insert into tblCategories values('House')

GO
Insert into tblProjects values('House Mission',1,'building house','Bathroom, kitchen, livingroom, bedroom etc....','link anh')
-------
GO
Insert into tblDomainList values('title here','link anh','content here')

GO
Insert into tblServiceOffered values(1,'offerName here','15000','Link anh','offer shortdescription here','offer desciption here')
-------
GO
Insert into tblAccounts values('Dungda','dungda','Doan Dung','099999','Hanoi-vietnam','yahoo.com',1,0)

GO
Insert into tblOrderStatus values('Not Start')
Insert into tblOrderStatus values('Up coming')
Insert into tblOrderStatus values('Accomplished ')


GO
Insert into tblOrders values('01-01-2012','Hanoi-vietnam','Noname','99999',1,1,999)
Insert into tblOrders values('01-01-2012','Hanoi-vietnam1','Noname1','99999',1,1,2222)
Insert into tblOrders values('01-01-2012','Hanoi-vietnam2','Noname2','99999',1,1,33333)

select *from tblOrders where orderID = 1
SELECT s.orderStaName FROM tblOrders o INNER JOIN tblOrderStatus s ON o.OrderId = s.orderStaID where o.OrderId = 2
SELECT * FROM tblOrders o INNER JOIN tblOrderStatus s ON o.OrderId = s.orderStaID where o.OrderId = 2
SELECT o.orderId, o.orderShipAddress, o.orderReceivedName, o.orderReceivedPhone, s.orderStaName, o.orderTotalPrice FROM tblOrders o INNER JOIN tblOrderStatus s ON o.OrderId = s.orderStaID where o.OrderId = 3



select *from tblOrderStatus